cd "/Users/tlandon/Box/LAUSD Files/"
pause off

use "data/common_core_data/ccd2002.dta", clear
// 2002 - 2005
foreach num of numlist 2/5{
	use "data/common_core_data/ccd200`num'.dta", clear

	keep if fipst == "06"
	rename seasch0`num' cdscode 

	destring ncessch fipst leaid schno stid0`num' cdscode type0`num' status0`num' , replace

	encode gslo0`num', generate(gsloa0`num') label(gslo0`num')
	encode gshi0`num', generate(gshia0`num') label(gshi0`num')

	foreach var of varlist locale0`num' level0`num' titlei0`num' magnet0`num' chartr0`num' conum0`num' stitli0`num'{
		replace `var' = "" if `var' == "N"
		destring `var', replace
	}

	keep ncessch fipst leaid schno stid0`num' cdscode type0`num' status0`num' locale0`num' ///
		conum0`num' fte0`num' gsloa0`num' gshia0`num'  ///
		stitli0`num' magnet0`num' chartr0`num' frelch0`num' redlch0`num' totfrl0`num' migrnt0`num' pk0`num' kg0`num' ///
		g010`num' g020`num' g030`num' g050`num' g060`num' g070`num' g080`num' g090`num' g100`num' g110`num' g120`num' ug0`num' ///
		member0`num' am0`num' asian0`num' hisp0`num' black0`num' white0`num' toteth0`num' puptch0`num' totgrd0`num' ///
		schnam0`num' lcity0`num' lzip0`num' lzip40`num' latcod0`num' loncod0`num' coname0`num' conum0`num'

	foreach var of varlist _all{
		capture confirm string variable `var'
		if !_rc {
			display `var'
		}
		else {
			replace `var' = . if `var' < 0
		}
	}
	rename *0`num' *


	gen year = 200`num'
	duplicates tag cdscode, gen(dups)

	drop if dups == 1 & (gsloa == 14 | gshia == 14)
	drop if dups == 1 & g09 == 0 & g12 > 0 
	drop dups
	duplicates tag cdscode, gen(dups)	
	tab dups
	pause

	save "data/common_core_data/clean/ccd200`num'_clean.dta", replace
	
}


// 2006 - 2007
foreach num of numlist 6/7{
	use "data/common_core_data/ccd200`num'.dta", clear

	keep if fipst == "06"
	rename seasch0`num' cdscode 

	destring ncessch fipst leaid schno stid0`num' cdscode type0`num' status0`num' , replace

	encode gslo0`num', generate(gsloa0`num') label(gslo0`num')
	encode gshi0`num', generate(gshia0`num') label(gshi0`num')

	foreach var of varlist level0`num' titlei0`num' magnet0`num' chartr0`num' conum0`num' stitli0`num'{
		replace `var' = "" if `var' == "N" | `var' == "M" | `var' == ""
		destring `var', replace
	}

	keep ncessch fipst leaid schno stid0`num' cdscode type0`num' status0`num' ///
		conum0`num' fte0`num' gsloa0`num' gshia0`num'  ///
		stitli0`num' magnet0`num' chartr0`num' frelch0`num' redlch0`num' totfrl0`num' migrnt0`num' pk0`num' kg0`num' ///
		g010`num' g020`num' g030`num' g050`num' g060`num' g070`num' g080`num' g090`num' g100`num' g110`num' g120`num' ug0`num' ///
		member0`num' am0`num' asian0`num' hisp0`num' black0`num' white0`num' toteth0`num' puptch0`num' totgrd0`num' ///
		schnam0`num' lcity0`num' lzip0`num' lzip40`num' latcod0`num' loncod0`num' coname0`num' conum0`num'

	foreach var of varlist _all{
		capture confirm string variable `var'
		if !_rc {
			display `var'
		}
		else {
			replace `var' = . if `var' < 0
		}
	}
	
	rename *0`num' *


	gen year = 200`num'
	duplicates tag cdscode, gen(dups)

	drop if dups == 1 & (gsloa == 14 | gshia == 14)
	drop dups
	duplicates tag cdscode, gen(dups)	
	tab dups
	pause

	save "data/common_core_data/clean/ccd200`num'_clean.dta", replace

	
}

// 2008 - 2009
foreach num of numlist 8/9 {
	use "data/common_core_data/ccd200`num'.dta", clear
	
	keep if fipst == "06"
	rename seasch0`num' cdscode 

	destring ncessch fipst leaid schno stid0`num' cdscode type0`num' status0`num' , replace

	encode gslo0`num', generate(gsloa0`num') label(gslo0`num')
	encode gshi0`num', generate(gshia0`num') label(gshi0`num')

	foreach var of varlist level0`num' titlei0`num' magnet0`num' chartr0`num' conum0`num' stitli0`num'{
		replace `var' = "" if `var' == "N" | `var' == "M" | `var' == ""
		destring `var', replace
	}

	keep ncessch fipst leaid schno stid0`num' cdscode type0`num' status0`num' ///
		conum0`num' fte0`num' gsloa0`num' gshia0`num'  ///
		stitli0`num' magnet0`num' chartr0`num' frelch0`num' redlch0`num' totfrl0`num' pk0`num' kg0`num' ///
		g010`num' g020`num' g030`num' g050`num' g060`num' g070`num' g080`num' g090`num' g100`num' g110`num' g120`num' ug0`num' ///
		member0`num' am0`num' asian0`num' hisp0`num' black0`num' white0`num' toteth0`num' ///
		schnam0`num' lcity0`num' lzip0`num' lzip40`num' latcod0`num' loncod0`num' coname0`num' conum0`num'
	
	foreach var of varlist _all{
		capture confirm string variable `var'
		if !_rc {
			display `var'
		}
		else {
			replace `var' = . if `var' < 0
		}
	}


	gen year = 200`num'
	duplicates tag cdscode, gen(dups)

	drop if dups == 1 & (gsloa == 14 | gshia == 14)
	drop if dups == 1 & member0`num' == .
	drop dups
	duplicates tag cdscode, gen(dups)	
	tab dups
	pause
	rename *0`num' *
	
	save "data/common_core_data/clean/ccd200`num'_clean.dta", replace
}

// 2010 - 2013
foreach num of numlist 10/13 {
	use "data/common_core_data/ccd20`num'.dta", clear
	pause
	keep if fipst == "06"
	rename seasch cdscode 

	destring ncessch fipst leaid schno stid cdscode type status , replace

	encode gslo, generate(gsloa) label(gslo)
	encode gshi, generate(gshia) label(gshi)

	foreach var of varlist level titlei magnet chartr conum stitli{
		replace `var' = "" if `var' == "N" | `var' == "M" | `var' == ""
		destring `var', replace
	}

	keep ncessch fipst leaid schno stid cdscode type status ///
		conum fte gsloa gshia  ///
		stitli magnet chartr frelch redlch totfrl pk kg ///
		g01 g02 g03 g05 g06 g07 g08 g09 g10 g11 g12 ug ///
		member am asian hisp black white toteth ///
		schnam lcity lzip lzip4 latcod loncod coname conum
	
	foreach var of varlist _all{
		capture confirm string variable `var'
		if !_rc {
			display `var'
		}
		else {
			replace `var' = . if `var' < 0
		}
	}


	gen year = 200`num'
	duplicates tag cdscode, gen(dups)

	drop if dups == 1 & (gsloa == 14 | gshia == 14)
	drop dups
	duplicates tag cdscode, gen(dups)	
	tab dups
	pause
	
	save "data/common_core_data/clean/ccd20`num'_clean.dta", replace
}



// 2014
use "data/common_core_data/ccd2014_1.dta", clear
merge 1:1 ncessch using "data/common_core_data/ccd2014_2.dta"
drop _merge

merge 1:1 ncessch using "data/common_core_data/ccd2014_3.dta"
drop _merge

merge 1:1 ncessch using "data/common_core_data/ccd2014_4.dta"
keep if _merge == 3
drop _merge

keep if fipst == "06"

encode gslo, generate(gsloa) label(gslo)
encode gshi, generate(gshia) label(gshi)

keep ncessch st_leaid schid st_schid gsloa gshia level virtual charter_text ///
	pkoffered kgoffered g1offered g2offered g3offered g4offered g5offered ///
	g6offered g7offered g8offered g9offered g10offered g11offered g12offered ///
	g13offered totfrl frelch redlch pk kg g01 g02 g03 g04 g05 g06 g07 ///
	g08 g09 g10 g11 g12 g13 ug member am as hi bl wh hp tr fte ///
	sch_name lcity lzip lzip4

replace level = "" if level == "N"
destring ncessch st_leaid schid st_schid level, replace

foreach var of varlist virtual charter_text pkoffered kgoffered g1offered ///
			g2offered g3offered g4offered g5offered g6offered g7offered ///
			g8offered g9offered g10offered g11offered g12offered g13offered{
	replace `var' = "1" if `var' == "Y" | `var' == "Yes"
	replace `var' = "0" if `var' == "N" | `var' == "No"
	destring `var', replace
}

	foreach var of varlist _all{
		capture confirm string variable `var'
		if !_rc {
			display `var'
		}
		else {
			replace `var' = . if `var' < 0
		}
	}

gen year = 2014
rename st_schid cdscode

	duplicates tag cdscode, gen(dups)

	drop if dups == 1 & (gsloa == 14 | gshia == 14)
	drop dups
	duplicates tag cdscode, gen(dups)	
	tab dups
	pause	

rename as asian
rename hi hisp
rename bl black
rename wh white
rename hp pacific
rename tr tworaces


save "data/common_core_data/clean/ccd2014_clean.dta", replace
